package JurisPro.modelo.banco;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.sql.Timestamp;
import JurisPro.modelo.Agenda;
import JurisPro.modelo.FilaEventos;



public class AgendaDAO {
	
	public static AgendaDAO singleton = null;
	
	public static AgendaDAO getInstance() {
		if(singleton == null)
			singleton = new AgendaDAO();
		return AgendaDAO.singleton;
	}
	
	
	public boolean insertAgenda(Agenda agenda) {

		Timestamp dataSql = new java.sql.Timestamp(agenda.getData().getTime());
		String sql = "INSERT INTO Agenda (idUsuario, data, descricao, local) "
				+ "VALUES (?, ?, ?, ?); ";
		boolean error = false;
		
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		try {
			state.setInt(1, agenda.getIdUsuario());
			state.setTimestamp(2, dataSql);
			state.setString(3, agenda.getDescricao());
			state.setString(4, agenda.getLocal());
			error = !state.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return error;
	}
	
	public int updateAgenda(Agenda agenda) {
		java.sql.Timestamp dataSql = new java.sql.Timestamp(agenda.getData().getTime());
		String sql = "UPDATE Agenda SET idUsuario = ?, data = ?, descricao = ?, local = ? WHERE IDAtividade = ?;";																	
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		int rows = 0;
		try {
			state.setInt(1, agenda.getIdUsuario());
			state.setTimestamp(2, dataSql);
			state.setString(3, agenda.getDescricao());
			state.setString(4, agenda.getLocal());
			state.setInt(5, agenda.getIdAtividade());
			rows = state.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows;			
	}

	
	public ArrayList<Agenda> selectAgendabyId(int id){
		String sql = "SELECT idAtividade, idUsuario, " +
				"data, descricao, local FROM Agenda where idUsuario = ? ORDER BY data ASC; ";
		Agenda agenda = null;
		ArrayList<Agenda> compromissos = new ArrayList<Agenda>();		
		try {
			PreparedStatement state = Conexao.getInstance().getPrepState(sql);
			state.setInt(1, id);			
			ResultSet rs = state.executeQuery();			
			while (rs.next()){
				Date data = rs.getTimestamp("data");
								
				agenda = new Agenda(rs.getInt("idAtividade"), rs.getInt("idUsuario"),
						data,rs.getString("descricao"),rs.getString("local"));
				
				compromissos.add(agenda);
				
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return compromissos;
	}
	
	public ArrayList<Agenda> selectAgendaAll(){
		String sql = "SELECT * FROM Agenda";
		ResultSet rs = Conexao.getInstance().selectComand(sql);
		Agenda agenda = null;
		ArrayList<Agenda> compromissos = new ArrayList<Agenda>();
		
			
		try {
			while (rs.next()){
								
				agenda = new Agenda(rs.getInt("idAtividade"), rs.getInt("idUsuario"),
						rs.getTimestamp("data"),rs.getString("descricao"),rs.getString("local"));
				compromissos.add(agenda);
				
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return compromissos;
	}
	
	public Agenda selectAgendabyIdAtividade(int idAtividade){
		String sql = "SELECT idAtividade, idUsuario, data, descricao, local FROM Agenda where IDAtividade = ? ORDER BY data ASC; ";
		Agenda agenda = null;
		try {
			PreparedStatement state = Conexao.getInstance().getPrepState(sql);
			state.setInt(1, idAtividade);			
			ResultSet rs = state.executeQuery();		
			while (rs.next()){
				
				Date data = rs.getTimestamp("data");
				agenda = new Agenda(rs.getInt("idAtividade"), rs.getInt("idUsuario"),
						data, rs.getString("descricao"),rs.getString("local"));			
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return agenda;
	
	}
	
	public int deleteAgenda(int id){
		String sql = "DELETE FROM Agenda where IDAtividade = ?; ";
		int rows = 0;
		try {
			PreparedStatement state = Conexao.getInstance().getPrepState(sql);
			state.setInt(1, id);
			rows = state.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}								
		return rows;
	}
	
	public ArrayList<FilaEventos> selectAgendaToday(int IDUsuario) {
		String sql = "(SELECT p.autor AS autor, p.parte AS parte, p.cliente AS cliente, e.data AS data1, " +
		"e.descricao AS descricao, e.local AS local FROM Evento AS e INNER JOIN Processo AS p ON e.numProcesso = p.numProcesso "+
		"WHERE e.IDUsuario = ? and e.data >= GETDATE()-1 and e.data < GETDATE()+1) union all "+
		"(SELECT  'Pessoal' AS autor, null AS parte, null AS cliente, a.data AS data1, a.descricao AS descricao, a.local AS local "+ 
		"FROM Agenda AS a WHERE a.IDUsuario = ? and a.data >= GETDATE()-1 and a.data < GETDATE()+1 ) order by data1";
		
		
		FilaEventos evento = null;
		ArrayList<FilaEventos> eventos = new ArrayList<FilaEventos>();
			
		try {
			PreparedStatement state = Conexao.getInstance().getPrepState(sql);
			state.setInt(1, IDUsuario);
			state.setInt(2, IDUsuario);		
			ResultSet rs = state.executeQuery();		
			while (rs.next()) {	
				Date data = rs.getTimestamp("data1");
					
				if(rs.getString("autor").equalsIgnoreCase("Pessoal"))
				       evento = new FilaEventos("Pessoal", data , rs.getString("descricao"), rs.getString("local"));
				else{
					String cliente;
					if(rs.getBoolean("cliente"))
						cliente = rs.getString("autor");
					else 
						cliente = rs.getString("parte");
					
					
					evento = new FilaEventos(cliente, data, rs.getString("descricao"), rs.getString("local"));
				}
					
				eventos.add(evento);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return eventos;
	}

	
}

